<?php if (!defined('BASEPATH')) exit('No direct script access allowed'); 
Class Admin_Model extends Model 
{
	function Admin_Model()
	{
		parent::Model();
	}
	
	function get_last_ten_Register()
	{
		$w = $this->db->query("SELECT A.KD_USER, A.USERNAME, B.ID, D.AKSES, B.NAMA, CASE WHEN AKTIF = '0' THEN 'Not Active' ELSE 'Active' end as AKTIF
								FROM zusers A
		INNER JOIN ( 	SELECT NIM AS ID, NAMA_MAHASISWA AS NAMA FROM m_mahasiswa
		UNION ALL
		SELECT NIP AS ID, NAMA_PEGAWAI AS NAMA FROM M_PEGAWAI ) B ON A.ID = B.ID
		INNER JOIN ZAKSES_USER C ON A.KD_USER = C.KD_USER
		INNER JOIN ZAKSES D ON C.KD_AKSES = D.KD_AKSES
								WHERE A.AKTIF = '0'");
		return $w->result();
	}
	
	function update($kduser)
	{
		$data = array('aktif' => '1');
		$this->db->where('kd_user', $kduser);
		$this->db->update('zusers', $data);
		return true;
	}
	
	function appPublish($id)
	{
		$data = array('publish' => '1');
		$this->db->where('id_collection', $id);
		$this->db->update('m_koleksi', $data);
	}
	
	function appApprove($id)
	{
		$data = array('approve' => '1');
		$this->db->where('id_collection', $id);
		$this->db->update('m_koleksi', $data);
		return true;
	}
	
	function count_all_num_rows_jurusan()
	{
		return $this->db->count_all('m_jurusan');
	}
	function count_all_num_rows_subKoleksi()
	{
		return $this->db->count_all('m_sub_tipe_koleksi');
	}
	
	function count_all_num_row_mhs()
	{
		return $this->db->count_all('m_mahasiswa');
	}
	
	function count_all_num_row_tipe()
	{
		return $this->db->count_all('m_tipe_koleksi');
	}
	
	function count_all_num_row_dosen()
	{
		return $this->db->count_all('m_pegawai');
	}
	
	function count_all_num_row_Keahlian()
	{
		return $this->db->count_all('m_keahlian_dosen');
	}
	
	function count_all_num_row_gelar()
	{
		return $this->db->count_all('m_gelar');
	}
	
	function doSavingJurusan($idJurusan, $nameJurusan, $kdFakultas)
	{
		$data = array(
			'kd_jurusan' => $idJurusan,
			'nama_jurusan' => $nameJurusan,
			'kd_fakultas' => $kdFakultas);
		$this->db->insert('m_jurusan', $data);
		return true;
	}
	function doSavingKeahlian($kd, $nama)
	{
		$data = array(
			'kd_keahlian' => $kd,
			'nama_keahlian' => $nama);
		$this->db->insert('m_keahlian_dosen', $data);
		return true;
	}
	function doSavingSubKoleksi($kdSub, $namaSub)
	{
		$data = array(
			'kd_sub_tipe_koleksi' => $kdSub,
			'nama_sub_koleksi' => $namaSub);
		$this->db->insert('m_sub_tipe_koleksi', $data);
		return true;
	}
	
	function doSavingTipeKoleksi($kdProdi, $namProdu)
	{
		$data = array(
			'kd_type_koleksi' => $kdProdi,
			'nama_type' => $namProdu);
		$this->db->insert('m_tipe_koleksi', $data);
		return true;
	}
	
	function doSavingGelar($kd, $nama)
	{
		$data = array(
			'kd_gelar' => $kd,
			'nama_gelar' => $nama);
		$this->db->insert('m_gelar', $data);
		return true;
	}
	
	function doSavingMahasiswa($nim, $nama, $tglLhr, $alamat,$jk, $thnAjrn, $prodi,$gelar, $nip)
	{
		$data = array(
			'nim' => $nim,
			'nama_mahasiswa' => $nama,
			'TGLLAHIR' => $tglLhr,
			'alamat' => $alamat,
			'kd_jenis_kelamin' =>$jk,
			'kd_tahun_ajaran' =>$thnAjrn,
			'kd_jurusan' =>$prodi,
			'kd_gelar' =>$gelar,
			'nip' => $nip);
		$this->db->insert('m_mahasiswa', $data);
	}
	
	function allowDownload($nim)
	{
		$result = $this->db->query("select a.ALLOW
		from trustee_download a
		left join zusers b on a.KD_USER = b.KD_USER and a.USERNAME = b.USERNAME
		left join m_mahasiswa c on b.id	= c.NIM WHERE c.nim = '$nim'");
		
		return $result;
	}
	
	function get_ten_jenis_koleksi($limit,$offset)
	{
		$this->db->select('kd_type_koleksi, nama_type');
		$this->db->limit($limit,$offset);
		
		return $this->db->get('m_tipe_koleksi');
	}
	
	function get_ten_sub_jenis_koleksi($limit,$offset)
	{
		$this->db->select('kd_sub_tipe_koleksi, nama_sub_koleksi');
		$this->db->limit($limit,$offset);
		
		return $this->db->get('m_sub_tipe_koleksi');
	}
	
	function get_tipe_by_kd($kdtipe)
	{
		$this->db->select('kd_type_koleksi, nama_type');
		$this->db->where('kd_type_koleksi', $kdtipe);
		return $this->db->get('m_tipe_koleksi');
	}
	
	function get_Subtipe_by_kd($kdtipe)
	{
		$this->db->select('kd_sub_tipe_koleksi, nama_sub_koleksi');
		$this->db->where('kd_sub_tipe_koleksi', $kdtipe);
		return $this->db->get('m_sub_tipe_koleksi');
	}
	
	function do_allow($nim)
	{
		$zusers = $this->db->query("select kd_user, username from zusers where id= '$nim'");
		if ($zusers->num_rows() > 0)
		{
			foreach($zusers->result() as $a)
			{
				$data = array ('allow'=>1);
				$this->db->where("kd_user",$a->kd_user);
				$this->db->where("username",$a->username);
				$sel1 = $this->db->get("trustee_download");
				if ($sel1->num_rows() == 1){
					$this->db->where("kd_user",$a->kd_user);
					$this->db->where("username",$a->username);
					$this->db->update("trustee_download", $data);
				}else{
					$dataInsert = array ('kd_user'=> $a->kd_user, 'username'=> $a->username, 'allow'=>1);
					$this->db->insert("trustee_download", $dataInsert);
				}
			}
			return true;
		}
		else{ return false; }
	}

	function do_notallow($nim)
	{
		$this->db->select('kd_user, username');
		$this->db->where('id',$nim);
		$zusers = $this->db->get('zusers');
		if ($zusers->num_rows() >0)
		{
			foreach($zusers->result() as $a)
			{
				$data = array ('allow'=>0);
				$this->db->where("kd_user",$a->kd_user);
				$this->db->where("username",$a->username);
				$this->db->update("trustee_download", $data);
			}
			return true;
		}
		else{ return false; }
	}
	
	function get_ten_keahlian($limit,$offset)
	{
		$this->db->select('kd_keahlian, nama_keahlian');
		$this->db->limit($limit,$offset);
		
		return $this->db->get('m_keahlian_dosen');
	}
	
	function get_ten_gelar($limit,$offset)
	{
		$this->db->select('kd_gelar, nama_gelar');
		$this->db->limit($limit,$offset);
		
		return $this->db->get('m_gelar');
	}
	
	function get_mhs_nim($nim)
	{
		// $this->db->select('nim, nama_mahasiswa, tgllahir, alamat');
		// $this->db->where('nim', $nim);
		// return $this->db->get('m_mahasiswa');
		$query = $this->db->query("select nim, nama_mahasiswa, date_format(tgllahir, '%d/%m/%Y') as tgllahir, alamat from m_mahasiswa where nim = '$nim'");
		return $query;
	}
	
	function get_dos_nip($nip)
	{
		$this->db->select('nip, nama_pegawai, kd_unit_kerja');
		$this->db->where('nip', $nip);
		return $this->db->get('m_pegawai');
	}
	
	
	function get_jurusan_by_kd($kdJur)
	{
		$this->db->select('kd_jurusan, nama_jurusan');
		$this->db->where('kd_jurusan', $kdJur);
		return $this->db->get('m_jurusan');
	}
	
	function get_keahlian_by_kd($kd)
	{
		$this->db->select('kd_keahlian, nama_keahlian');
		$this->db->where('kd_keahlian', $kd);
		return $this->db->get('m_keahlian_dosen');
	}
	
	
	function get_gelar_by_kd($kd)
	{
		$this->db->select('kd_gelar, nama_gelar');
		$this->db->where('kd_gelar', $kd);
		return $this->db->get('m_gelar');
	}
	
	
	function deleteJurusan($kdJur)
	{
		$this->db->where('kd_jurusan', $kdJur);
		$this->db->delete('m_jurusan');
		return true;
	}

	function deleteKeahlian($kd)
	{
		$this->db->where('kd_keahlian', $kd);
		$this->db->delete('m_keahlian_dosen');
		return true;
	}

	
	function deleteJenisSubKoleksi($kdSub)
	{
		$this->db->where('kd_sub_tipe_koleksi', $kdSub);
		$this->db->delete('m_sub_tipe_koleksi');
		return true;
	}

	
	function deleteTipeKoleksi($kdTypeKoleksi)
	{
		$this->db->where('kd_type_koleksi', $kdTypeKoleksi);
		$this->db->delete('m_tipe_koleksi');
	}
	
	function deleteMhs($nim)
	{
		$this->db->where('nim', $nim);
		$this->db->delete('m_mahasiswa');
	}
	
	function deleteDosen($nip)
	{
		$this->db->where('nip', $nip);
		$this->db->delete('m_dosen');
	}
	
	function doSavingDosen($nip, $nama, $kdKeahlian)
	{
		$data = array(
			'nip' => $nip,
			'kd_keahlian' => $kdKeahlian,
			'nama' => $nama);
		$this->db->insert('m_dosen', $data);
		return true;
	}
	
	function updateTipeKoleksi($kdTipe,$nameTipe)
	{
		$data = array('nama_type' => $nameTipe);
		$this->db->where('kd_type_koleksi', $kdTipe);
		$this->db->update('m_tipe_koleksi', $data);
	}
	
	function updateJurusan($kdJur,$nameJur)
	{
		$data = array('nama_jurusan' => $nameJur);
		$this->db->where('kd_jurusan', $kdJur);
		$this->db->update('m_jurusan', $data);
		return true;
	}
	
	function uppdateMhs($nim, $data)
	{
		$this->db->where('nim', $nim);
		$this->db->update('m_mahasiswa', $data);
		return true;
	}
	
	function get_ten_jurusan()
	{
		$w = $this->db->query("select kd_jurusan, nama_jurusan from m_jurusan
						limit 10");
		
		return $w->result();
	}
	
	function updateKeahlian($kd,$nama)
	{
		$data = array('nama_keahlian' => $nama);
		$this->db->where('kd_keahlian', $kd);
		$this->db->update('m_keahlian_dosen', $data);
		return true;
	}
	
	function get_ten_wait_publish()
	{
		$w = $this->db->query("select a.TITLE,  b.nip_pembimbing, b.nama_pembimbing as NAMA, a.ID_COLLECTION, case when a.approve = '1' then 'Yes' else 'No' end as Approve, 
						case when a.publish = '1' then 'Yes' else 'No' end as Publish, a.ID_COLLECTION
						from m_koleksi a
						left join pembimbing b on a.ID_COLLECTION = b.ID_COLLECTION
						left join penulis c on a.id_collection = c.ID_COLLECTION
						left join m_pegawai d on b.nip_pembimbing = d.NIP
						where (a.publish is null) or (a.approve is null)
						group by a.TITLE,  b.nip_pembimbing, b.nama_pembimbing , a.ID_COLLECTION, a.approve, a.ID_COLLECTION
						limit 10
						");
		
		return $w->result();
	}
	
	function get_ten_mhs($limit,$offset)
	{
		$this->db->select("NIM, NAMA_MAHASISWA, TGLLAHIR, ALAMAT");
		$this->db->limit($limit);
		
		return $this->db->get('M_MAHASISWA');
	}
	function get_ten_dos($limit)
	{
		$query = $this->db->query("SELECT A.NIP, B.KD_KEAHLIAN, A.NAMA_PEGAWAI, C.NAMA_KEAHLIAN 
		FROM M_PEGAWAI A
		LEFT JOIN DET_KEAHLIAN B ON A.NIP = B.NIP
		LEFT JOIN M_KEAHLIAN_DOSEN C ON C.KD_KEAHLIAN = B.KD_KEAHLIAN limit $limit");
		
		return $query;
	}
	
	function doUpdateGelar($kd,$nama)
	{
		$data = array('nama_gelar' => $nama);
		$this->db->where('kd_gelar', $kd);
		$this->db->update('m_gelar', $data);
		return true;
	}
	
	function dodeleteGelar($kd)
	{
		$this->db->where('kd_gelar', $kd);
		$this->db->delete('m_gelar');
		return true;
	}

	
}